if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_selfMotionsynuser')
          and type in ('P','PC'))
DROP PROCEDURE bd_proc_selfMotionsynuser
GO
CREATE PROCEDURE bd_proc_selfMotionsynuser
as
BEGIN

-- 关闭打印受影响行数
SET NOCOUNT ON;

--创建数据库服务
-- exec sp_addlinkedserver  'syn_server','','SQLOLEDB','121.40.101.29';
-- exec sp_addlinkedsrvlogin 'syn_server','false',null,'sa','super123!@#';

	--删除人员同步表数据
	DELETE from bd_selfMotionSynUserData;
	--添加人员同步表数据
-- 	select
-- 		* into bd_selfMotionSynUserData
-- 	from
-- 	OPENDATASOURCE (
-- 		'SQLOLEDB',
-- 		'Data Source=121.40.101.29;USER ID = sa ;Password=super123!@#'
-- 	).scm_main_dev.dbo.bd_ehrUserData AS Rowset_1;
insert into bd_selfMotionSynUserData select *  from syn_server.scm_main_jczx.dbo.bd_ehrUserData;

	  --临时差异表
  CREATE TABLE #diff(
    user_no VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
    user_lname VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
		cost_center VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
		user_email VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
		user_depname VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
		english_name VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
		work_beginDay VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
    is_same INT DEFAULT 0
  )

	delete from bd_manual_syn_user

  INSERT INTO #diff ( user_no, user_lname,cost_center,user_email,user_depname,english_name,work_beginDay, is_same)
  SELECT user_no, user_lname,cost_center,user_email,user_depname,english_name,work_beginDay, 0 FROM dt_user

  --与e-HR对比的差异
  MERGE
  INTO #diff p
  USING bd_selfMotionSynUserData s
  ON p.user_no = s.STAFF_NO
    AND p.user_lname = s.STAFF_NAME
		and isnull(p.cost_center,-1)=isnull(s.cost_center,-1)
		and isnull(p.user_email,-1)=isnull(s.E_MAIL,-1)
		and isnull(p.user_depname,-1)=isnull(s.CITY_DESCRIPTION,-1)
		and isnull(p.english_name,-1)=isnull(s.Expr1,-1)
		and isnull(p.work_beginDay,null)=isnull(s.DATE_JOIN,null)
  WHEN MATCHED
    THEN UPDATE
      SET p.is_same = 1
-- 			相同得更新为1，表示数据是相同得
  ;

  --加到最终差异表
  MERGE
  INTO bd_manual_syn_user p
  USING (SELECT * FROM #diff WHERE is_same=0) s
  ON p.user_no = s.user_no
		AND p.user_lname = s.user_lname
		and isnull(p.cost_center,-1)=isnull(s.cost_center,-1)
		and isnull(p.user_email,-1)=isnull(s.user_email,-1)
		and isnull(p.user_depname,-1)=isnull(s.user_depname,-1)
		and isnull(p.english_name,-1)=isnull(s.english_name,-1)
		and isnull(p.work_beginDay,null)=isnull(s.work_beginDay,null)
  WHEN MATCHED
    THEN UPDATE
      SET p.user_lname = s.user_lname
				 ,p.cost_center=s.cost_center
         ,p.user_email = s.user_email
				 ,p.user_depname = s.user_depname
				 ,p.english_name = s.english_name
				 ,p.work_beginDay = s.work_beginDay
  WHEN NOT MATCHED BY TARGET
    THEN INSERT ( user_no, user_lname,cost_center,user_email,user_depname,english_name,work_beginDay)
        VALUES ( user_no, user_lname,cost_center,user_email,user_depname,english_name,work_beginDay)
  ;

 --同步人员信息
 update d set  d.user_lname=e.STAFF_NAME,
        d.cost_center=e.cost_center,
		d.user_email=e.E_MAIL,
		d.user_depname=e.CITY_DESCRIPTION,
		d.english_name=e.Expr1,
		d.work_beginDay=e.DATE_JOIN
		from dt_user d left join bd_selfMotionSynUserData e on d.user_no=e.STAFF_NO
	where d.user_no in (select distinct b.user_no from bd_manual_syn_user b inner join bd_selfMotionSynUserData e on b.user_no=e.STAFF_NO)


--  清除临时表
	DROP TABLE #diff;

--关闭数据库服务
-- exec sp_dropserver 'syn_server','droplogins';



-- 	select @result as result

-- 打开打印受影响行数
SET NOCOUNT OFF;
END;
